<?php
ini_set('memory_limit', '-1');
ini_set('max_execution_time', 86400);

define('_JEXEC', 1);
define('DS', DIRECTORY_SEPARATOR);

if (file_exists(dirname(__FILE__) . '/defines.php')) {
	include_once dirname(__FILE__) . '/defines.php';
}

if (!defined('_JDEFINES')) {
	define('JPATH_BASE', dirname(__FILE__));
	require_once JPATH_BASE.'/includes/defines.php';
}

require_once JPATH_BASE.'/includes/framework.php';

// Instantiate the application.
$app = JFactory::getApplication('site');

// Initialise the application.
$app->initialise();

require_once JPATH_BASE.DS.'administrator'.DS.'components'.DS.'com_wrd'.DS.'classes'.DS.'utilities'.DS.'reportProvider.php';
require_once JPATH_BASE.DS.'administrator'.DS.'components'.DS.'com_wrd'.DS.'classes'.DS.'utilities'.DS.'pointProvider.php';
require_once JPATH_BASE.DS.'administrator'.DS.'components'.DS.'com_wrd'.DS.'classes'.DS.'PHPExcel.php';

// Init excel
$objPHPExcel = new PHPExcel();
// Create a first sheet, representing sales data
$objPHPExcel->setActiveSheetIndex(0);
$activeSheet = $objPHPExcel->getActiveSheet();
$activeSheet->mergeCells('A1:O1');
$activeSheet->setCellValue('A1', 'VIP Membership Club Detail Report');
$activeSheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->getStyle('A1')->getFont()->setSize(24);
$activeSheet->getStyle('A1')->getFont()->setBold(true);

/************** FILL HEADER **************/
$index = 5;
$activeSheet->setCellValue('A'.$index, 'MEMBERNO');
$activeSheet->setCellValue('B'.$index, 'Title');
$activeSheet->setCellValue('C'.$index, 'NAME');
$activeSheet->setCellValue('D'.$index, 'Password');
$activeSheet->setCellValue('E'.$index, 'Date of Birth');
$activeSheet->setCellValue('F'.$index, 'Nationality');
$activeSheet->setCellValue('G'.$index, 'Member ID');
$activeSheet->setCellValue('H'.$index, 'Member Since');
$activeSheet->setCellValue('I'.$index, 'EMAIL');
$activeSheet->setCellValue('J'.$index, 'MOBILE');
$activeSheet->setCellValue('K'.$index, 'Address');
$activeSheet->setCellValue('L'.$index, 'District');
$activeSheet->setCellValue('M'.$index, 'CITY');
$activeSheet->setCellValue('N'.$index, 'Apply from');
$activeSheet->setCellValue('O'.$index, 'Pick up outlet');

$cols = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N','O');
foreach($cols as $item)
{
	if($item == 'K')
    {
        setBackgroundColor($activeSheet, $item, 5, 35);
    }
    
	else if($item == 'N' || $item == 'O')
    {
        setBackgroundColor($activeSheet, $item, 5, 45);
    }
    else
    {
        setBackgroundColor($activeSheet, $item, 20);   
    }
}

// End init excel

$fromTo = strval($_GET['f']).' to '.strval($_GET['t']);
$datefrom = explode('-', strval($_GET['f']));
$dateto = explode('-', strval($_GET['t']));

$datefrom[0] = array('from'=>$datefrom[0], 'to'=>$dateto[0]);
$datefrom[1] = array('from'=>$datefrom[1], 'to'=>$dateto[1]);
$datefrom[2] = array('from'=>$datefrom[2], 'to'=>$dateto[2]);
//echo '<pre>';print_r($datefrom);die;
/******** Set up query ********/
$db = JFactory::getDbo();
$query = reportProvider::membershipClubDetailQuery($datefrom[1], $datefrom[0], $datefrom[2], strval($_GET['s']));


if(intval($_GET['l']) > 0)
{
    $db->setQuery($query['items'], 0, intval($_GET['l']) );   
}
else
{
    $db->setQuery($query['items'] );   
}
$data = $db->loadObjectList();
//echo count($data);echo '<pre>';print_r($data);die;
if(!empty($data))
{
    $status = '';
    switch(strval($_GET['s']))
    {
        case 'active':  $status = 'Active'; break;
        case 'nonactive':  $status = 'Inactive'; break;
        case 'pickup':  $status = 'Pick up'; break;
        case 'nonpickup':  $status = 'Non pick up'; break;
        case 'newjoin':  $status = 'New join'; break; 
    }
    
	$fill = $activeSheet->getStyle('A5:O5')->getFill();
    $fill->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
    $fill->getStartColor()->setARGB('cc99ff'); 
	
    $activeSheet->mergeCells('A3:K3');
    $activeSheet->setCellValue('A3', 'Total: '.count($data).'. From '.$fromTo.'. Status: '.$status);   
    $style = $activeSheet->getStyle('A3')->getFont();
    $style->setSize(16);
    $style->setBold(true);
    $style->setColor( new PHPExcel_Style_Color( 'CC0000' ) );
    
    /*********** BIND TO EXCEL ***********/
    $index = 5;
    $size = count($data);
    for($i=0; $i<$size; $i++)
    {

        $index++;
     	$year = explode('-', $data[$i]->dob);
     	$pass = $data[$i]->member_id.$year[0];
     	$firstnum = substr($data[$i]->phone, 0,1);
     	if($firstnum != 0)
     		$data[$i]->phone = '0'.$data[$i]->phone;

        $activeSheet->setCellValue('A'.$index,'V'.$data[$i]->member_id);
		$activeSheet->setCellValue('B'.$index,$data[$i]->prefix);
		$activeSheet->setCellValue('C'.$index,$data[$i]->l_name.' '.$data[$i]->f_name);
		$activeSheet->setCellValue('D'.$index,$pass);
		$activeSheet->getStyle('D'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$activeSheet->setCellValue('E'.$index,$data[$i]->dob);
		$activeSheet->setCellValue('F'.$index,$data[$i]->countryname);
		$activeSheet->setCellValue('G'.$index,"8800".$data[$i]->member_id."01");
		$activeSheet->setCellValue('H'.$index,$data[$i]->registerDate);
		$activeSheet->setCellValue('I'.$index,$data[$i]->p_email);
		$activeSheet->setCellValueExplicit('J'.$index,$data[$i]->phone,PHPExcel_Cell_DataType::TYPE_STRING);
		$activeSheet->setCellValue('K'.$index,(($data[$i]->address == '') ? $data[$i]->c_addr : $data[$i]->address));
        $activeSheet->setCellValue('L'.$index,(($data[$i]->address == '') ? $data[$i]->c_districname : $data[$i]->districname));
        $activeSheet->setCellValue('M'.$index,(($data[$i]->address == '') ? $data[$i]->c_cittyname : $data[$i]->cittyname));
		$activeSheet->setCellValue('N'.$index,$data[$i]->noutletname);
        $activeSheet->setCellValue('O'.$index,$data[$i]->outletname);
    }
    unset($data);
}

$fileName = 'vip_membership_club_detail_'.date('m-d-Y');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(JPATH_BASE.DS.'tmp'.DS.$fileName.'.xlsx');

$fullPath = JPATH_ROOT.DS.'tmp'.DS.$fileName.'.xlsx';
$fsize = filesize($fullPath);

header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename="'.$fileName.'.xlsx"');
header('Content-Transfer-Encoding: binary');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
header('Content-Length: ' . $fsize);
ob_clean();
flush();
readfile( $fullPath );
exit;          


function setBackgroundColor(&$activeSheet, $colName, $colIndex, $width=15)
{
    $activeSheet->getColumnDimension($colName)->setWidth($width);    
    $style = $activeSheet->getStyle($colName.$colIndex);
    $style->getFont()->setSize(12);
    $style->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
}
